/*
 * Golazo MA4
 * Copyright (C) 2008 Golazo MASHUP AWARD 4th team.
 * http://code.google.com/p/golazo/
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License 
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */

/**
 * Golazo
 */
Golazo = {};

// HOME
Golazo.HOME = '/opt/golazo';

// Golazo.MEXTRACTR_APIKEY
Golazo.MEXTRACTR_APIKEY = 'YOUR MEXTRACTR_APIKEY';

// Golazo.PICTURE_MAXBYTES
Golazo.PICTURE_MAXBYTES = 30 * 1024;

// Golazo.PICTURE_TYPES
Golazo.PICTURE_TYPES = [
  { mimeType: 'image/jpeg', extension: 'jpg' },
  { mimeType: 'image/pjpeg', extension: 'jpg' },
  { mimeType: 'image/gif', extension: 'gif' },
  { mimeType: 'image/png', extension: 'png' },
  { mimeType: 'image/x-png', extension: 'png' }
];

// Golazo.WHERE_TOKYO
Golazo.WHERE_TOKYO = {
  latitude: 35.689718,
  longitude: 139.691699,
  altitude: 15 
};

/**
 * Golazo.setup
 */
Golazo.setup = function() {
  
  var commands = Jaxer.File
    .read(Golazo.HOME + '/db/golazo.sql')
    .split(/;/g);
  
  commands.forEach(function(command) {
    command = $.trim(command);
    if (command != '')
      Jaxer.DB.execute(command);
  });
}
Golazo.setup();

/**
 * Golazo.dispatch
 */
Golazo.dispatch = function(fn) {
  try {
    fn(Jaxer.request, Jaxer.response, Jaxer.session);
  } catch (e) {
    throw new Error(''+e);
  }
}

/**
 * Golazo.Geo
 */
Golazo.Geo = {};

/**
 * Golazo.Geo.get
 */
Golazo.Geo.get = function(text) {

  // Google Maps API - Geocoding via HTTP
  // http://code.google.com/apis/maps/documentation/services.html#Geocoding_Direct
  var url = 'http://maps.google.com/maps/geo?' +
    $.param({ q: text, output: 'xml', key: Golazo.GOOGLE_APIKEY });

  var xml;
  try {
    xml = Jaxer.Web.get(url, { as: 'xml' });
  } catch (e) {
    throw new Error(''+e);
  }

  // jQuery XML to JSON Plugin
  // http://fyneworks.com/jquery/xml-to-json/
  var kml = $.xml2json(xml, true);
  
  //var code = kml
  //  .Response[0]
  //  .Status[0]
  //  .code[0]
  //  .text;
  
  var places = (kml.Response[0].Placemark || [])
	.map(function(placemark) {

      //var address = placemark
	  //  .AddressDetails[0]
	  //  .Country[0]
	  //  .AddressLine[0]
	  //  .text;
		
	  var coordinates = placemark
	    .Point[0]
	    .coordinates[0]
	    .text
	    .split(/,/);

      return {
		//address: address,
		latitude: coordinates[1],
		longitude: coordinates[0],
		altitude: Golazo.WHERE_TOKYO.altitude
	  };
  });
    
  return places;
};

/**
 * Golazo.Kinds
 */
Golazo.Kinds = {}

/**
 * Golazo.Kinds.get
 */
Golazo.Kinds.get = function(text, url) {
  var response = [];
  
  if (url) {

    // 住所抽出API 
    // http://okilab.jp/project/location/2007/02/api_3.html
    var url = 'http://okilab.jp/project/location/address/address.do?' +
      $.param({ url: url });

    var addr;
	try {
      addr = eval('(' + Jaxer.Web.get(url) + ')');
    } catch (e) {
      throw new Error(''+e);
    }
	
	if (addr.length > 0)
	  text += ' ' + addr.join(' ');
  }

  if (text == '')
    return response;

//// 先頭から256文字を対象にする
//text = text.substr(0, 256);

  // LocoSticker 位置表現特定API 
  // http://okilab.jp/project/location/2007/11/locosticker_api.html
  var url = 'http://api.locosticker.jp/v1/extract_place/';
  var data = $.param({ text: text, selection: 'on' });

  var clusters;
  try {
    clusters = eval('(' + Jaxer.Web.post(url, data) + ')')
	  .result_select;
  } catch (e) {
    throw new Error(''+e);
  }

  // クラスタをフラットな配列に変換する
  clusters.forEach(function(cluster) {
    cluster.forEach(function(where) {
      where.label = where.text;
	  where.latitude = where.lat;
	  where.longitude = where.lng;
	  where.altitude = Golazo.WHERE_TOKYO.altitude;
	  response.push(where);
	});
  });

  // 住所よりもスポットを優先し、重みでソートする
  response.sort(function(a, b) {
    if (a.type == b.type)
     return a.weight - b.weight;
	else if (a.type == 'spot')
	 return -1;
	else
	 return 1;
  });

  // 位置表現が特定できたら継続しない
  if (response.length > 0)
    return response; // found.

  // Mextractr Web API 
  // http://www.emetadata.net/doc
  var url = 'http://api.emetadata.net/mextractr';
  var data = $.param({ text: text, apikey: Golazo.MEXTRACTR_APIKEY });
  
  var xml;
  try {
    xml = Jaxer.Web.post(url, data, { as: 'xml' });
  } catch (e) {
    throw new Error(''+e);
  }

  // jQuery XML to JSON Plugin
  // http://fyneworks.com/jquery/xml-to-json/
  var feed = $.xml2json(xml, true);
  if (feed.message)
    throw new Error(feed.message[0].text);

  // 位置表現だけを抜き出す
  feed.entry.forEach(function(entry) {
	if (entry.where) {						  
      entry.where.forEach(function(where) {
	    response.push({ label: where.valueString });
	  });
	}
  });

  // 位置表現から緯度経度を求める
  Golazo.each(response, function(i, where) {
    var geos;
	try {
	  geos = Golazo.Geo.get(where.label);
	} catch (e) {
	  throw new Error(''+e);
	}

    var geo = geos[0] || {};
    where.latitude = geo.latitude;
    where.longitude = geo.longitude;
    where.altitude = geo.altitude;
  
	if (where.latitude)
	  return false; // break 
  });

  return response;
};

/**
 * Golazo.User
 */
Golazo.User = function() {
  this.initialize.apply(this, arguments);
};

/**
 * Golazo.User.findAll
 */ 
Golazo.User.findAll = function(offset, limit, options) {
  var rows = [];

  var statement = 
    'SELECT ' +
    '  * ' +
    'FROM ' +
    '  Users ';
  var params = [];

  var keyword = options.keyword;
  if (keyword) {
    statement += 
	  'WHERE (' +
	  '  UserId LIKE ? OR ' +
	  '  Nickname LIKE ? OR ' +
	  '  Note LIKE ? ' +
	  ') ',
	params.push('%' + keyword + '%');
	params.push('%' + keyword + '%');
	params.push('%' + keyword + '%');
  }

  var exclude = options.exclude;
  if (exclude) {
    statement += statement.match(/WHERE/)
	  ? '  AND UserId != ? '
	  : 'WHERE UserId != ? '
	params.push((exclude && exclude.userId) || exclude);
  }

  statement += 
    'ORDER BY ' + 
    '  UserId ASC ' +
    'LIMIT ? OFFSET ? ',
  params.push(limit);
  params.push(offset);

  var cursor = Jaxer.DB.execute(statement, params);

  while (cursor.isValidRow()) {
    var row = new Golazo.User();
    cursor.columns.forEach(function(column) {
	  row[column.substr(0, 1).toLowerCase() + column.substr(1)] =								
        cursor.fieldByName(column);
    });
	row.password = '';
    row.found = true;
    rows.push(row);
    cursor.next();
  }

  return rows;
};

/**
 * Golazo.User.count
 */ 
Golazo.User.count = function(options) {
  var rows = [];
  
  var statement = 
    'SELECT ' +
    '  COUNT(*) Total ' +
    'FROM ' +
    '  Users ';
  var params = [];

  var keyword = options.keyword;
  if (keyword) {
    statement += 
	  'WHERE (' +
	  '  UserId LIKE ? OR ' +
	  '  Nickname LIKE ? OR ' +
	  '  Note LIKE ? ' +
	  ') ',
	params.push('%' + keyword + '%');
	params.push('%' + keyword + '%');
	params.push('%' + keyword + '%');
  }

  var exclude = options.exclude;
  if (exclude) {
    statement += statement.match(/WHERE/)
	  ? '  AND UserId != ? '
	  : 'WHERE UserId != ? '
	params.push((exclude && exclude.userId) || exclude);
  }

  var cursor = Jaxer.DB.execute(statement, params);

  return cursor.isValidRow() ? cursor.fieldByName('Total') : 0;
};

/**
 * Golazo.User.find
 */
Golazo.User.find = function(userId) {
  var rows = [];
  
  var cursor = Jaxer.DB.execute(
    'SELECT ' +
    '  * ' +
    'FROM ' +
    '  Users ' +
    'WHERE ' + 
    '  UserId = ? ',
  [
    userId || ''
  ]);

  while (cursor.isValidRow()) {
    var row = new Golazo.User();
    cursor.columns.forEach(function(column) {
	  row[column.substr(0, 1).toLowerCase() + column.substr(1)] =								
        cursor.fieldByName(column);
    });
	row.password = '';
    row.found = true;
    rows.push(row);
    cursor.next();
  }
  
  return rows[0]; // single row.
}

/**
 * Golazo.User.auth
 */
Golazo.User.auth = function(user, password) {
  var rows = [];
  
  var cursor = Jaxer.DB.execute(
    'SELECT ' +
    '  * ' +
    'FROM ' +
    '  Users ' +
    'WHERE ' + 
    '  UserId = ? AND Password = ?',
  [
    (user && user.userId) || user,
	password
  ]);

  while (cursor.isValidRow()) {
    var row = new Golazo.User();
    cursor.columns.forEach(function(column) {
	  row[column.substr(0, 1).toLowerCase() + column.substr(1)] =								
        cursor.fieldByName(column);
    });
	row.password = '';
    row.found = true;
    rows.push(row);
    cursor.next();
  }
  
  return rows[0]; // single row.
}

/**
 * Golazo.User.prototype.initialize
 */
Golazo.User.prototype.initialize = function(userId) {
  this.userId = userId;
  this.password = '';
  this.email = '';
  this.nickname = '';
  this.note = '';
  this.picture = 'images/gray-48x48.gif';
  this.carwings = 0;
  this.carwingsToken = '';
  this.found = false;
};

/**
 * Golazo.User.prototype.equals
 */
Golazo.User.prototype.equals = function(user) {
  return this.userId == ((user && user.userId) || user);
};

/**
 * Golazo.User.prototype.toString
 */
Golazo.User.prototype.toString = function() {
  var values = [];
  for each(var value in this)
    if (!$.isFunction(value))
  	  values.push(value);
  return values.join(' ');
};

/**
 * Golazo.User.prototype.sign
 */
Golazo.User.prototype.sign = function() {
  return ''+Math.abs(Jaxer.Util.CRC32.getStringCRC(''+this));
};

/**
 * Golazo.User.prototype._create
 */
Golazo.User.prototype._create = function() {
  var now = new Date();

  this.created = now;
  this.modified = now;
  this.lastLogin = now;

  Jaxer.DB.execute(
    'INSERT INTO Users ( ' +
    '  UserId, ' +
    '  Password, ' +
	'  Email, ' +
    '  Nickname, ' +
	'  Note, ' +
	'  Picture, ' +
    '  Created, ' +
    '  Modified, ' +
    '  LastLogin, ' +
    '  Carwings, ' +
    '  CarwingsToken ' +
    ') VALUES (' +
    '  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ' +
    ') ',
  [
    this.userId,
    this.password,
	this.email,
    this.nickname,
	this.note,
	this.picture,
    this.created,
    this.modified,
    this.lastLogin,
    this.carwings,
    this.carwingsToken
  ]);

  this.found = true;
}

/**
 * Golazo.User.prototype._update
 */
Golazo.User.prototype._update = function() {
  var now = new Date();

  this.modified = now;

  Jaxer.DB.execute(
    'UPDATE Users SET ' +
    //'  Password = ?, ' +
    '  Email = ?, ' +
    '  Nickname = ?, ' +
    '  Note = ?, ' +
	'  Picture = ?, ' +
	'  Modified = ?, ' +
	'  LastLogin = ?, ' +
    '  Carwings = ?, ' +
    '  CarwingsToken = ? ' +
    'WHERE ' +
    '  UserId = ? ',
  [
    //this.password,
	this.email,
    this.nickname,
	this.note,
	this.picture,
    this.modified,
    this.lastLogin,
    this.carwings,
    this.carwingsToken,
    this.userId
  ]);

  this.found = true;
}

/**
 * Golazo.User.prototype.save
 */
Golazo.User.prototype.save = function() {
  this.found ? this._update() : this._create();
}

/**
 * Golazo.User.prototype.updatePassword
 */
Golazo.User.prototype.updatePassword = function(password) {
  var now = new Date();

  this.modified = now;

  Jaxer.DB.execute(
    'UPDATE Users SET ' +
    '  Password = ?, ' +
	'  Modified = ? ' +
    'WHERE ' +
    '  UserId = ? ',
  [
    password,
	this.modified,
    this.userId
  ]);
}

/**
 * Golazo.User.prototype.delete
 */
Golazo.User.prototype.delete = function() {
  
  Golazo.UserLog.deleteByMe(this.userId);
  Golazo.Action.deleteByMe(this.userId)

  Jaxer.DB.execute(
    'DELETE FROM ' +
    '  UserGroup ' +
	'WHERE ' +
	'  UserId = ? OR TrustedId = ? ',
  [
    this.userId,
    this.userId
  ]);

  Jaxer.DB.execute(
    'DELETE FROM ' +
    '  Users ' +
	'WHERE ' +
	'  UserId = ? ',
  [
    this.userId
  ]);

  this.found = false;
}

/**
 * Golazo.User.prototype.findByTrusted
 */
Golazo.User.prototype.findByTrusted = function() {
  var rows = [];

  var cursor = Jaxer.DB.execute(
    'SELECT ' +
    '  * ' +
    'FROM ' +
    '  Users ' +
	'WHERE ' +
	'  UserId IN (SELECT TrustedId FROM UserGroup WHERE UserId = ?) ' +
    'ORDER BY ' + 
    '  UserId ASC ',
  [
    this.userId
  ]);

  while (cursor.isValidRow()) {
    var row = new Golazo.User();
    cursor.columns.forEach(function(column) {
	  row[column.substr(0, 1).toLowerCase() + column.substr(1)] =								
        cursor.fieldByName(column);
    });
    row.password = '';
	row.found = true;
    rows.push(row);
    cursor.next();
  }

  return rows;
}

/**
 * Golazo.User.prototype.findByTrustedTo
 */
Golazo.User.prototype.findByTrustedTo = function() {
  var rows = [];

  var cursor = Jaxer.DB.execute(
    'SELECT ' +
    '  * ' +
    'FROM ' +
    '  Users ' +
	'WHERE ' +
	'  UserId IN (SELECT UserId FROM UserGroup WHERE TrustedId = ?) ' +
    'ORDER BY ' + 
    '  UserId ASC ',
  [
    this.userId
  ]);

  while (cursor.isValidRow()) {
    var row = new Golazo.User();
    cursor.columns.forEach(function(column) {
	  row[column.substr(0, 1).toLowerCase() + column.substr(1)] =								
        cursor.fieldByName(column);
    });
    row.password = '';
    row.found = true;
    rows.push(row);
    cursor.next();
  }

  return rows;
}

/**
 * Golazo.User.prototype.trust
 */
Golazo.User.prototype.trust = function(user) {
  var now = new Date();

  Jaxer.DB.execute(
    'REPLACE INTO UserGroup ( ' +
    '  UserId, ' +
    '  TrustedId, ' +
    '  Created ' +
    ') VALUES (' +
    '  ?, ?, ? ' +
    ') ',
  [
    this.userId,
    user.userId || user,
    now
  ]);
}

/**
 * Golazo.User.prototype.distrust
 */
Golazo.User.prototype.distrust = function(user) {

  Jaxer.DB.execute(
    'DELETE FROM ' +
    '  UserActions ' +
	'WHERE ' +
	'  UserId = ? AND ' + 
	'  ActionId IN (SELECT ActionId FROM Actions WHERE CreatorId = ?) ',
  [
    this.userId,
    user.userId || user
  ]);

  Jaxer.DB.execute(
    'DELETE FROM ' +
    '  UserGroup ' +
	'WHERE ' +
	'  UserId = ? AND TrustedId = ? ',
  [
    this.userId,
    user.userId || user
  ]);
}

/**
 * Golazo.Action
 */
Golazo.Action = function() {
  this.initialize.apply(this, arguments);
};

/**
 * Golazo.Action.findAllByMe
 */
Golazo.Action.findAllByMe = function(me, offset, limit, options) {
  var rows = [];

  var statement = 
    'SELECT ' +
    '  * ' +
    'FROM ' +
    '  Actions ' +
	'WHERE ' +
	'  ActionId IN (SELECT ActionId FROM UserActions WHERE UserId = ?) ' +
    'ORDER BY ';
  var params = [
    me.userId || me
  ];

  var where = options.where;
  if (where) {
    statement += 
      '  ABS(? - Latitude) * ABS(? - Latitude) + ' +
	  '  ABS(? - Longitude) * ABS(? - Longitude) ASC, ';
    params.push(where.latitude);
    params.push(where.latitude);
    params.push(where.longitude);
    params.push(where.longitude);
  }

  statement += 
	'  Modified DESC ' +
    'LIMIT ? OFFSET ? ',
  params.push(limit);
  params.push(offset);

  var cursor = Jaxer.DB.execute(statement, params);

  while (cursor.isValidRow()) {
    var row = new Golazo.Action();
    cursor.columns.forEach(function(column) {
	  row[column.substr(0, 1).toLowerCase() + column.substr(1)] =								
        cursor.fieldByName(column);
    });
    row.found = true;
    rows.push(row);
    cursor.next();
  }

  return rows;
};

/**
 * Golazo.Action.countByMe
 */
Golazo.Action.countByMe = function(me) {
  var rows = [];

  var cursor = Jaxer.DB.execute(
    'SELECT ' +
    '  COUNT(*) Total ' +
    'FROM ' +
    '  Actions ' +
	'WHERE ' +
	'  ActionId IN (SELECT ActionId FROM UserActions WHERE UserId = ?) ',
  [
    me.userId || me
  ]);

  return cursor.isValidRow() ? cursor.fieldByName('Total') : 0;
};

/**
 * Golazo.Action.findAllByMeAndYou
 */
Golazo.Action.findAllByMeAndYou = function(me, you, offset, limit) {
  var rows = [];

  var cursor = Jaxer.DB.execute(
    'SELECT ' +
    '  * ' +
    'FROM ' +
    '  Actions ' +
	'WHERE ' +
    '  ActionId IN ( ' +
    '    SELECT ' +
    '      ActionId ' +
    '    FROM (' +
	'      (SELECT ActionId FROM UserActions WHERE UserId = ?) t1 JOIN ' +
	'      (SELECT ActionId FROM UserActions WHERE UserId = ?) t2 USING (ActionId) ' +
	'  )) ' +
    'ORDER BY ' + 
    '  Modified DESC ' +
    'LIMIT ? OFFSET ? ',
  [
    me.userId || me,
	you.userId || you,
	limit,
	offset
  ]);

  while (cursor.isValidRow()) {
    var row = new Golazo.Action();
    cursor.columns.forEach(function(column) {
	  row[column.substr(0, 1).toLowerCase() + column.substr(1)] =								
        cursor.fieldByName(column);
    });
    row.found = true;
    rows.push(row);
    cursor.next();
  }

  return rows;
};

/**
 * Golazo.Action.countByMeAndYou
 */
Golazo.Action.countByMeAndYou = function(me, you) {
  var rows = [];

  var cursor = Jaxer.DB.execute(
    'SELECT ' +
    '  COUNT(*) Total ' +
    'FROM ' +
    '  Actions ' +
	'WHERE ' +
    '  ActionId IN ( ' +
    '    SELECT ' +
    '      ActionId ' +
    '    FROM (' +
	'      (SELECT ActionId FROM UserActions WHERE UserId = ?) t1 JOIN ' +
	'      (SELECT ActionId FROM UserActions WHERE UserId = ?) t2 USING (ActionId) ' +
	'  )) ',
  [
    me.userId || me,
	you.userId || you,
  ]);

  return cursor.isValidRow() ? cursor.fieldByName('Total') : 0;
}

/**
 * Golazo.Action.find 
 */
Golazo.Action.find = function(actionId) {
  var rows = [];
  
  var cursor = Jaxer.DB.execute(
    'SELECT ' +
    '  * ' +
    'FROM ' +
    '  Actions ' +
    'WHERE ' + 
    '  ActionId = ? ',
  [
    actionId
  ]);

  while (cursor.isValidRow()) {
    var row = new Golazo.Action();
    cursor.columns.forEach(function(column) {
	  row[column.substr(0, 1).toLowerCase() + column.substr(1)] =								
        cursor.fieldByName(column);
    });
    row.found = true;
    rows.push(row);
    cursor.next();
  }
  
  return rows[0]; // single row.
}

/**
 * Golazo.Action.deleteByMe
 */
Golazo.Action.deleteByMe = function(me) {

  Jaxer.DB.execute(
    'DELETE FROM ' +
    '  UserActions ' +
	'WHERE ' +
	'  UserId = ? OR ' + 
	'  ActionId IN (SELECT ActionId FROM Actions WHERE CreatorId = ?) ',
  [
    me.userId || me,
    me.userId || me
  ]);

  Jaxer.DB.execute(
    'DELETE FROM ' +
    '  Actions ' +
    'WHERE ' +
    '  CreatorId = ? ',
  [
    me.userId || me
  ]);
}

/**
 * Golazo.Action.prototype.initialize
 */
Golazo.Action.prototype.initialize = function(user) {
  this.note = '';
  this.latitude = 0;
  this.longitude = 0;
  this.altitude = 0;
  this.creatorId = (user && user.userId) || user;
  this.found = false;
};

/**
 * Golazo.Action.prototype._create
 */
Golazo.Action.prototype._create = function() {
  var now = new Date();

  this.created = now;
  this.modified = now;

  Jaxer.DB.execute(
    'INSERT INTO Actions ( ' +
    '  Note, ' +
    '  Latitude, ' +
    '  Longitude, ' +
	'  Altitude, ' +
	'  CreatorId, ' +
    '  Created, ' +
    '  Modified ' +
    ') VALUES (' +
    '  ?, ?, ?, ?, ?, ?, ? ' +
    ') ',
  [
    this.note,
    this.latitude,
    this.longitude,
	this.altitude,
	this.creatorId,
    this.created,
    this.modified
  ]);

  this.actionId = Jaxer.DB.lastInsertId;
  this.found = true;
};

/**
 * Golazo.Action.prototype._update
 */
Golazo.Action.prototype._update = function() {
  var now = new Date();

  this.modified = now;

  Jaxer.DB.execute(
    'UPDATE Actions SET ' +
    '  Note = ?, ' +
    '  Latitude = ?, ' +
    '  Longitude = ?, ' +
	'  Altitude = ?, ' +
    '  Modified = ? ' +
    'WHERE ' +
    '  ActionId = ? ',
  [
    this.note,
    this.latitude,
    this.longitude,
	this.altitude,
    this.modified,
	this.actionId
  ]);

  this.found = true;
}

/**
 * Golazo.Action.prototype.save
 */
Golazo.Action.prototype.save = function() {
  this.found ? this._update() : this._create();
}

/**
 * Golazo.Action.prototype.delete
 */
Golazo.Action.prototype.delete = function() {

  Jaxer.DB.execute(
    'DELETE FROM ' +
    '  UserActions ' +
    'WHERE ' +
    '  ActionId = ? ',
  [
    this.actionId
  ]);

  Jaxer.DB.execute(
    'DELETE FROM ' +
    '  Actions ' +
    'WHERE ' +
    '  ActionId = ? ',
  [
    this.actionId
  ]);

  this.found = false;
}

/**
 * Golazo.Action.prototype.findBySubscribed
 */
Golazo.Action.prototype.findBySubscribed = function() {
  var rows = [];

  var cursor = Jaxer.DB.execute(
    'SELECT ' +
    '  * ' +
    'FROM ' +
    '  Users ' +
	'WHERE ' +
	'  UserId IN (SELECT UserId FROM UserActions WHERE ActionId = ?) ' +
    'ORDER BY ' + 
    '  UserId ASC ',
  [
    this.actionId
  ]);

  while (cursor.isValidRow()) {
    var row = new Golazo.User();
    cursor.columns.forEach(function(column) {
	  row[column.substr(0, 1).toLowerCase() + column.substr(1)] =								
        cursor.fieldByName(column);
    });
    row.password = '';
	row.found = true;
    rows.push(row);
    cursor.next();
  }

  return rows;
}

/**
 * Golazo.Action.prototype.subscribe
 */
Golazo.Action.prototype.subscribe = function(user) {
  var now = new Date();

  Jaxer.DB.execute(
    'REPLACE INTO UserActions ( ' +
    '  UserId, ' +
    '  ActionId, ' +
    '  Created ' +
    ') VALUES (' +
    '  ?, ?, ? ' +
    ') ',
  [
    user.userId || user,
    this.actionId,
    now
  ]);
}

/**
 * Golazo.Action.prototype.unsubscribe
 */
Golazo.Action.prototype.unsubscribe = function(user) {
  
  Jaxer.DB.execute(
    'DELETE FROM ' +
    '  UserActions ' +
	'WHERE ' +
	'  ActionId = ? AND UserId = ? ',
  [
    this.actionId,
    user.userId || user,
  ]);
}

/**
 * Golazo.Action.prototype.unsubscribeNotCreator
 */
Golazo.Action.prototype.unsubscribeNotCreator = function() {
  
  Jaxer.DB.execute(
    'DELETE FROM ' +
    '  UserActions ' +
	'WHERE ' +
	'  ActionId = ? AND UserId != ? ',
  [
    this.actionId,
	this.creatorId
  ]);
}

/**
 * Golazo.UserLog
 */
Golazo.UserLog = function() {
  this.initialize.apply(this, arguments);
};

/**
 * Golazo.UserLog.deleteByMe 
 */
Golazo.UserLog.deleteByMe = function(me) {

  Jaxer.DB.execute(
    'DELETE FROM ' +
    '  UserLogs ' +
	'WHERE ' +
	'  UserId = ? ',
  [
    me.userId || me
  ]);
}

/**
 * Golazo.UserLog.prototype.initialize
 */
Golazo.UserLog.prototype.initialize = function(user) {
  this.userId = (user && user.userId) || user;
  this.latitude = 0;
  this.longitude = 0;
  this.found = false;
};

/**
 * Golazo.UserLog.prototype.save
 */
Golazo.UserLog.prototype.save = function() {
  var now = new Date();

  this.created = new Date();

  Jaxer.DB.execute(
    'INSERT INTO UserLogs ' +
    '  (UserId, Latitude, Longitude, Created) ' +
    'VALUES ' +
    '  (?, ?, ?, ?) ',
  [
    this.userId,
    this.latitude,
    this.longitude,
    this.created
  ]);
  
  this.logId = Jaxer.DB.lastInsertId;
  this.found = true;
};

/**
 * $.fn.metaRefresh
 */
$.fn.metaRefresh = function(url, delay) {
  $('<meta />')
    .attr('http-equiv', 'refresh')
    .attr('content', delay + '; url=' + url)
	.prependTo('head');
  return this;
};

/**
 * $.fn.maps
 */
$.fn.maps = function() {
  var head = $('head')[0];
  
  $('<script />')
    .attr('type', 'text/javascript')
    .attr('src', 'http://www.google.com/jsapi?' + $.param({ key: Golazo.GOOGLE_APIKEY }))
	.each(function() {
	  head.appendChild(this);
	});
	
  $('<script />')
    .attr('type', 'text/javascript')
	.text('google.load("maps", "2");')
	.each(function() {
	  head.appendChild(this);
	});
  
  return this;
};

/**
 * $.fn.bookmarklet
 */ 
$.fn.bookmarklet = function(url) {
  var href = "javascript:(function(){var w=window;var d=document;var u=w.location.href;var t=d.title;var n=d.selection?d.selection.createRange().text:(w.getSelection?w.getSelection():(d.getSelection?d.getSelection():''));location.href='"+url+"?url='+encodeURIComponent(u)+'&title='+encodeURIComponent(t)+'&note='+encodeURIComponent(n);})();";
  return this.attr('href', href);
};

/**
 * $.fn.pager
 */
$.fn.pager = function(url, startIndex, maxResults, totalResults) {

  var separater = url.indexOf('?') == -1 ? '?' : '&';
  var total = Math.floor((totalResults + (maxResults - 1)) / maxResults);
  var visited = Math.ceil(startIndex / maxResults) - 1;

  for (var i = 0; i < total; i++) {
	var param = {
	  startIndex: i * maxResults + 1,
	  maxResults: maxResults
	};
    
	var a = $('<a />')
      .attr('href', url + (url.match(/\?/) ? '&' : '?') + $.param(param))
	  .text(i + 1);
	$('<span />')
      .addClass('link')
      .append(a)
	  .appendTo(this);
  }

  $('span', this).eq(visited)
    .addClass('visited')
    .text(visited + 1);

  if (visited > 0) {
	var param = {
	  startIndex: (visited - 1) * maxResults + 1,
	  maxResults: maxResults
	};
    
	var a = $('<a />')
	  .attr('href', url + (url.match(/\?/) ? '&' : '?') + $.param(param))
	  .text('前へ');
	$('<span />')
      .addClass('link')
	  .append(a)
	  .prependTo(this);
  }

  if (visited + 1 < total) {
	var param = {
	  startIndex: (visited + 1) * maxResults + 1,
	  maxResults: maxResults
	};

    var a = $('<a />')
	  .attr('href', url + (url.match(/\?/) ? '&' : '?') + $.param(param))
	  .text('次へ');
	$('<span />')
      .addClass('link')
	  .append(a)
	  .appendTo(this);
  }
  
  var lastIndex = (startIndex - 1) + maxResults;
  if (lastIndex > totalResults)
    lastIndex = totalResults;
  
 $('<span />')
  .text([ totalResults, '件中', startIndex, '-', lastIndex, '件目' ].join(' '))
  .appendTo(this);

  return this;
};

/**
 * $.fn.layout
 */
$.fn.layout = function(me, you) {
//if (!you) you = me;

  var chapter = this
    .show();

//$('.chapter', '#content')
  $('.chapter')
    .not(chapter)
	.remove();

  // me (viewer)
  if (me) {

    $('h2', '#menubar')
	  .find('a.navi')
        .attr('href', me.carwings ? '/navioff.html' : '/navion.html')
		.end()
      .toggle();

    $('span', '#toolbar')
      .toggle();

//  $('div.widget:first', '#sidebar')
//	  .toggle();

    $('img.me')
	  .attr('alt', me.nickname || me.userId)
	  .attr('src', me.picture + '?' + me.modified.getTime());

	$('div.me, span.me, a.me')
	  .text(me.nickname || me.userId);
	  
	$('div.me.userId, span.me.userId')
	  .text(me.userId);

	$('div.me.nickname, span.me.nickname')
	  .text(me.nickname);

	$('div.me.note, span.me.note')
	  .text(me.note);

var aliasUrl = '/you.html?' + $.param({ userId: me.userId });
$('a', this).each(function(i, a) {
  if (a.href === Jaxer.Dir.resolveUrl(aliasUrl))
    a.href = '/me.html';
});

  }

  // you (owner)
  if (you) {

    $('img.you')
	  .attr('alt', you.nickname || you.userId)
	  .attr('src', you.picture + '?' + you.modified.getTime());

	$('div.you, span.you, a.you')
	  .text(you.nickname || you.userId);
	  
	$('div.you.userId, span.you.userId')
	  .text(you.userId);

	$('div.you.nickname, span.you.nickname')
	  .text(you.nickname);

	$('div.you.note, span.you.note')
	  .text(you.note);


  }

if (chapter.is('#you')) {

  var user = $('div.user', '#you')
    .attr('id', 'user_' + you.userId);

  var trusted = me.findByTrusted();

  var found = !!Golazo.find(trusted, function() {
    return you.equals(this);
  });

  if (found)
    $('.menubar > span', user)
      .toggle();
}

  return this;
};

/**
 * Jaxer.SessionManager.keyFromRequest
 */
Jaxer.SessionManager.keyFromRequest = function(appKey) {
  return Jaxer.request.headers['X-DCMGUID']
    || Jaxer.Util.Cookie.get(Jaxer.SessionManager.COOKIE_PREFIX + appKey)
    || [new Date().getTime(), Math.random()].join('').replace(/[^0-9]/g, '');
};

/**
 * Jaxer.SessionManager.keyToResponse
 */
Jaxer.SessionManager.keyToResponse = function(appKey, key) {
  return Jaxer.request.headers['X-DCMGUID']
    || Jaxer.Util.Cookie.set(Jaxer.SessionManager.COOKIE_PREFIX + appKey, key);
};
